from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, ForeignKey, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import json

Base = declarative_base()

class DTSTask(Base):
    __tablename__ = 'dts_tasks'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    type = Column(String, CheckConstraint("type IN ('migrate', 'sync')"), nullable=False)
    status = Column(String, 
                   CheckConstraint("status IN ('CREATED', 'PRECHECK', 'RUNNING', 'PAUSED', 'COMPLETED', 'FAILED')"),
                   default='CREATED')
    source_config = Column(Text, nullable=False)  # JSON string
    target_config = Column(Text, nullable=False)  # JSON string
    options = Column(Text)  # JSON string
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationship to runtime data
    runtime = relationship("DTSTaskRuntime", back_populates="task", uselist=False, cascade="all, delete-orphan")
    logs = relationship("DTSTaskLog", back_populates="task", cascade="all, delete-orphan")
    
    @property
    def source(self):
        return json.loads(self.source_config)
    
    @property
    def target(self):
        return json.loads(self.target_config)
    
    @property
    def options_dict(self):
        return json.loads(self.options) if self.options else None

class DTSTaskRuntime(Base):
    __tablename__ = 'dts_task_runtime'
    
    task_id = Column(Integer, ForeignKey('dts_tasks.id'), primary_key=True)
    checkpoint = Column(Text)  # JSON string
    delay_seconds = Column(Integer, default=0)
    qps = Column(Integer, default=0)
    last_error = Column(Text)
    
    # Relationship back to task
    task = relationship("DTSTask", back_populates="runtime")

class DTSTaskLog(Base):
    __tablename__ = 'dts_task_logs'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(Integer, ForeignKey('dts_tasks.id'))
    level = Column(String, CheckConstraint("level IN ('info', 'warn', 'error')"))
    message = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Relationship back to task
    task = relationship("DTSTask", back_populates="logs")